package com.example.gerenciadorestudo;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

public class SQliteHelper extends SQLiteOpenHelper{
	 private static String DB_PATH = "/data/data/com.example.gerenciadorestudo/databases/";
	
	 private static String DB_NAME = "gernEstudo";
	 
	 private SQLiteDatabase myDataBase; 
	 
	 private final Context myContext;
	

	public SQliteHelper(Context context){
		super(context, DB_NAME, null, 1);
		this.myContext = context;
		// TODO Auto-generated constructor stub
	}

	 public void createDataBase() throws IOException{
		 
	    	boolean dbExist = checkDataBase();
	 
	    	if(dbExist){
	    		//do nothing - database already exist
	    	}else{
	 
	    		//By calling this method and empty database will be created into the default system path
	               //of your application so we are gonna be able to overwrite that database with our database.
	        	this.getReadableDatabase();
	 
	        	try {
	 
	    			copyDataBase();
	 
	    		} catch (IOException e) {
	 
	        		throw new Error("Error copying database");
	 
	        	}
	    	}
	 
	    }
	 private boolean checkDataBase(){
	 
	    	File dbFile = new File(DB_PATH + DB_NAME);
	        return dbFile.exists();
	    }

	 private void copyDataBase() throws IOException{
		 
	    	//Open your local db as the input stream
	    	InputStream myInput = myContext.getAssets().open(DB_NAME);
	 
	    	// Path to the just created empty db
	    	String outFileName = DB_PATH + DB_NAME;
	 
	    	//Open the empty db as the output stream
	    	OutputStream myOutput = new FileOutputStream(outFileName);
	 
	    	//transfer bytes from the inputfile to the outputfile
	    	byte[] buffer = new byte[1024];
	    	int length;
	    	while ((length = myInput.read(buffer))>0){
	    		myOutput.write(buffer, 0, length);
	    	}
	 
	    	//Close the streams
	    	myOutput.flush();
	    	myOutput.close();
	    	myInput.close();
	 
	    }
	 
	  public void openDataBase() throws SQLException{
		  
	    	//Open the database
	        String myPath = DB_PATH + DB_NAME;
	    	myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.NO_LOCALIZED_COLLATORS | SQLiteDatabase.CREATE_IF_NECESSARY);

	 
	    }
	  
	  @Override
		public synchronized void close() {
	 
	    	    if(myDataBase != null)
	    		    myDataBase.close();
	 
	    	    super.close();
	 
		}
	 
		@Override
		public void onCreate(SQLiteDatabase db) {
	 
		}
	 
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	 
		}
		
		
		public void inserirMateria(Materia materia){
			ContentValues values = new ContentValues();
			
			values.put("nome", materia.getNome());
			values.put("codigo", materia.getCodigo());
			values.put("horaInicio", materia.getHoraInicio());
			values.put("horaFim", materia.getHoraFim());
			myDataBase.insert("materia",null,values);
				
		}
		
		 
		public void inserirAvaliacao(Avaliacao avaliacao){
			ContentValues values = new ContentValues();
			
			Cursor c = myDataBase.rawQuery("select max(_id) as max_id from avaliacao", null);
			int id = 1;
			
			 if(c.moveToFirst()){
			 
				 do{
					 id = c.getInt(0) + 1;
				 }while(c.moveToNext());   
			
			 }
			values.put("_id", id);
			values.put("id_materia", avaliacao.getId_materia());
			values.put("descricao", avaliacao.getDescricao());
			SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
			dateFormat.applyPattern("yyyy-MM-dd");
			values.put("data_avaliacao", dateFormat.format(avaliacao.getData()));
			myDataBase.insert("avaliacao",null,values);
			
			for(Assunto ast : avaliacao.getAssuntos()){
				inserirAssunto(ast, id);
			}
				
		}
		
		public void inserirAssunto(Assunto assunto, int id_avaliacao){
			ContentValues values = new ContentValues();
			
			values.put("id_avaliacao", id_avaliacao);
			values.put("descricao", assunto.getDescricao());
			values.put("concluido", assunto.getConcluido());


			myDataBase.insert("assunto",null,values);
				
		}
		
	 public ArrayList<Materia> listarMaterias(){  
			    
	    ArrayList<Materia> resultados = new ArrayList<Materia>(); 		 
	    String nome, codigo,hora, minuto;
	    Cursor c = myDataBase.rawQuery("select * from materia order by nome",null);  
	    		  
	    c.moveToFirst();  
	    
	    do{
	    	nome = c.getString(c.getColumnIndex("nome"));
	    	codigo = c.getString(c.getColumnIndex("codigo"));
	    	hora = c.getString(c.getColumnIndex("horaInicio"));
	    	minuto = c.getString(c.getColumnIndex("horaFim"));
	    	
	    	Materia materia = new Materia(nome, codigo, hora, minuto);
	    	resultados.add(materia);
	    }while(c.moveToNext());     
	    
	    return resultados;
	  }
	 
	 public ArrayList<Avaliacao> listarAvaliacoes(){  
		    
		    ArrayList<Avaliacao> resultados = new ArrayList<Avaliacao>(); 		 
		    String descricao;
		    int id_materia;
		    Date data = new Date();
		    Cursor c = myDataBase.rawQuery("select * from avaliacao order by data_avaliacao",null);  
		    		  
		    c.moveToFirst();  
		    
		    do{
		    	descricao = c.getString(c.getColumnIndex("descricao"));
		    	id_materia = c.getInt(c.getColumnIndex("id_materia"));
		    	
		    	
		    	
		    	try {
		    		
					String teste = c.getString(c.getColumnIndex("data_avaliacao"));
					SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
					data = dateFormat.parse(teste);
					dateFormat.applyPattern("dd/MM/yyyy");
					dateFormat.format(data);
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		    	
		    	
		    	Avaliacao avaliacao = new Avaliacao(id_materia, descricao, data, null);
		    	resultados.add(avaliacao);
		    }while(c.moveToNext());     
		    
		    return resultados;
		  }
	 
	 public int idMateria(String codigo){  
		    	 
		    int id = 0;
		    Cursor c = myDataBase.rawQuery("select _id from materia where codigo = \""+codigo+"\"",null);  
		    		  
		    c.moveToFirst();  
		    
		    do{
		    	id = c.getInt(c.getColumnIndex("_id"));

		    }while(c.moveToNext());     
		    
		    return id;
	}
	 
	 
	 public Avaliacao AvaliacaoByDescricao(String descricao){
		    int id_materia;
		    Date data = new Date();
		    Cursor c = myDataBase.rawQuery("select * from avaliacao where descricao = \""+descricao+"\" limit 1",null);  
		    Avaliacao avaliacao;
		    int id;
		    c.moveToFirst();  
		    
		    do{
		    	descricao = c.getString(c.getColumnIndex("descricao"));
		    	id_materia = c.getInt(c.getColumnIndex("id_materia"));
		    	id = c.getInt(c.getColumnIndex("_id"));
		    	
		    	try {
		    		
					String teste = c.getString(c.getColumnIndex("data_avaliacao"));
					SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
					data = dateFormat.parse(teste);
					dateFormat.applyPattern("dd/MM/yyyy");
					dateFormat.format(data);
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		    	
		    	ArrayList<Assunto> assuntos = listaAssuntosAvaliacao(id);
		    	
		    	avaliacao = new Avaliacao(id_materia, descricao, data, assuntos);
		    	
		    }while(c.moveToNext());     
		    
		    return avaliacao;
	}
	 
	 public ArrayList<Assunto> listaAssuntosAvaliacao(int id) {
		// TODO Auto-generated method stub
		ArrayList<Assunto> assuntos = new ArrayList<Assunto>();
		String descricao;
		int concluido;
		
		Cursor c = myDataBase.rawQuery("select * from assunto where id_avaliacao = "+id,null);
		
		c.moveToFirst();  
		
		do{
	    	descricao = c.getString(c.getColumnIndex("descricao"));
	    	concluido = c.getInt(c.getColumnIndex("concluido"));
	    	Assunto assunto = new Assunto(descricao, concluido);
	    	assuntos.add(assunto);
	    	
	    	
	    }while(c.moveToNext());
		
		return assuntos;
	}

	public Materia MateriaById(int id){  

		 	String nome;
		 	String codigo;
		 	String horaInicio;
		 	String horaFim;
		    Cursor c = myDataBase.rawQuery("select * from materia where _id = "+id,null);  		  
		    Materia materia;
		    
		    c.moveToFirst();  
		    
		    do{
		    	nome = c.getString(c.getColumnIndex("nome"));
		    	codigo = c.getString(c.getColumnIndex("codigo"));
		    	horaInicio = c.getString(c.getColumnIndex("horaInicio"));
		    	horaFim = c.getString(c.getColumnIndex("horaFim"));
		    	
		    }while(c.moveToNext());     
		    
		    materia = new Materia(nome, codigo, horaInicio, horaFim);
		    
		    return materia;
	}
	 

	 public ArrayList<String> listarNomeMaterias(){  
			    
	    ArrayList<String> resultados = new ArrayList<String>(); 		 
	    String nome;
	    Cursor c = myDataBase.rawQuery("select nome,codigo from materia order by nome",null);  
	    		  
	    c.moveToFirst();  
	    
	    do{
	    	nome = c.getString(c.getColumnIndex("codigo"))+"\n"+c.getString(c.getColumnIndex("nome"));

	    	resultados.add(nome);
	    }while(c.moveToNext());     
	    
	    return resultados;
	  }
	 
	 public ArrayList<String> listarNomeAvaliacoes(){
		 ArrayList<String> resultados = new ArrayList<String>();
		 String nome;
		 Cursor c = myDataBase.rawQuery("select descricao from avaliacao order by data_avaliacao", null);
		 if(c.moveToFirst()){
			 do{
				 nome = c.getString(c.getColumnIndex("descricao"));
				 resultados.add(nome);
			 }while(c.moveToNext());
		 }
		 return resultados;
	 }
	 
	 public void atualizarAssunto(String descricao, int concluido){
		 		 
		 
		 Cursor cu = myDataBase.rawQuery("update assunto set concluido = "+concluido+" where descricao = \""+descricao+"\"", null);
		 cu.moveToFirst();
		 cu.close();
	 }

}
